package org.javaforever.cookieshop.dao;

import java.util.Set;
import org.javaforever.cookieshop.domain.RoleBean;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import jakarta.transaction.Transactional;
public interface RoleDao extends JpaRepository<RoleBean, Long>{
	@Query("from RoleBean r")
	public List<RoleBean> listAllRoles() throws Exception;

	@Modifying
	@Query("delete from RoleBean r where r.id = :id")
	@Transactional
	public void deleteRole(Long id) throws Exception;

	@Modifying
	@Query("update RoleBean r set r.active = false where r.id = :id")
	@Transactional
	public void softDeleteRole(Long id) throws Exception;

	@Query("from RoleBean r where r.id = :id")
	public RoleBean findRoleById(Long id) throws Exception;

	@Query("from RoleBean r where r.roleName = :roleName")
	public RoleBean findRoleByRoleName(String roleName) throws Exception;

	@Query("from RoleBean r where r.active = true")
	public List<RoleBean> listActiveRoles() throws Exception;

	@Modifying
	@Query("update RoleBean r set r.active = not r.active where r.id = :id")
	@Transactional
	public void toggleRole(Long id) throws Exception;

	@Modifying
	@Query("update RoleBean r set r.active = true where r.id = :id")
	@Transactional
	public void activateRole(Long id) throws Exception;

	@Query("from RoleBean r where r.id = :id")
	public RoleBean findRoleFullInfoById(Long id) throws Exception;

	@Query("from RoleBean r where r.roleName like CONCAT(:shortName, '%')")
	public List<RoleBean> searchRoleIndexedNamesByShortName(String shortName) throws Exception;

	@Query("select count(*) as countNum from RoleBean r where r.active = true")
	public Integer countActiveRoleRecords() throws Exception;

	@Modifying
	@Query(value="insert into cs_role_permission ( role_id,permission_id) values (:roleId,:permissionId)", nativeQuery=true)
	@Transactional
	public Integer assignPermissionToRole(Long roleId,Long permissionId) throws Exception;

	@Modifying
	@Query(value="delete from cs_role_permission  where role_id = :roleId and permission_id = :permissionId", nativeQuery=true)
	@Transactional
	public Integer revokePermissionFromRole(Long roleId,Long permissionId) throws Exception;

	@Query(value="select distinct cs_roles.id,cs_roles.role_name,cs_roles.active from cs_roles,cs_user_role where cs_roles.id = cs_user_role.role_id and cs_user_role.user_id = :userId and cs_roles.active = true order by cs_roles.role_name asc ", nativeQuery=true)
	public Set<RoleBean> listActiveUserRolesUsingUserId(Long userId) throws Exception;

}
